﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace oqis.core.dbaccess
{
    public class ExcelAccess : OQISDBAccessBase
    {
        #region 类成员变量
 
        #endregion

        #region 类构建与初始化

        /// <summary>
        /// 类初始化
        /// </summary>
        /// <param name="sourceFile">Excel源文件</param>
        public ExcelAccess(string sourceFile, qiaoExcelVersion ver)
        {
            // 得到连接
            _dbConnection = GetExcelConnection(sourceFile, ver);
            if (_dbConnection != null) _dbCommand = this._dbConnection.CreateCommand();
            _dbAdapter = new OleDbDataAdapter((System.Data.OleDb.OleDbCommand)_dbCommand);
        }

        ///// <summary>
        ///// 类初始化
        ///// </summary>
        ///// <param name="sourceFile">Excel源文件</param>
        ///// <param name="password">密码</param>
        //public ExcelAccess(string sourceFile, string password)
        //{
        //    _dbConnection = GetExcelConnection(sourceFile, password);
        //    if (_dbConnection != null) this._dbCommand = _dbConnection.CreateCommand();
        //    _dbAdapter = new OleDbDataAdapter((System.Data.OleDb.OleDbCommand)_dbCommand);
        //}

        #endregion

        #region 类属性
 
        /// <summary>
        /// 数据库类型
        /// </summary>
        public override  qiaoDBMSType DBMSType { get { return  qiaoDBMSType.MSExcel; } }

        #endregion

        #region 继承的方法

        ///// <summary>
        ///// 执行数据库操作
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <returns></returns>
        //public override int ExcuteNoneQuery(string sql)
        //{
        //    if (this._dbCommand == null) { 
            
        //    }

        //    return _dbCommand.ExecuteNonQuery();
        //}

        ///// <summary>
        ///// 执行数据库操作，返回第一行记录
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <returns></returns>
        //public override object ExcuteScalar(string sql)
        //{
        //    return base.ExcuteScalar(sql);
        //}

        ///// <summary>
        ///// 获取数据集
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <returns></returns>
        //public override DataSet GetDataSet(string sql)
        //{
        //    return base.GetDataSet(sql);
        //}

        ///// <summary>
        ///// 获取数据表
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <returns></returns>
        //public override DataTable GetDataTable(string sql)
        //{
        //    return base.GetDataTable(sql);
        //}

        /// <summary>
        /// 更新数据表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public override bool UpdateTable(string tableName, DataTable table)
        {
            return base.UpdateTable(tableName, table);
        }
        #endregion

        #region 类公开方法

        /// <summary>
        /// 读取 excel 文件的所有工作表名
        /// </summary>
        /// <returns>返回Excel文件中所有的工作表，数组</returns> 
        public string[] GetExcelWorksheetNames()
        {
            // 判断
            if (this._dbConnection == null) return null;
            OleDbConnection xlsConn = (OleDbConnection)this._dbConnection;
            try
            {
                // 打开连接
                if (xlsConn.State == ConnectionState.Closed) xlsConn.Open();
                // 得到所有的表
                System.Data.DataTable dataTable = xlsConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                // 定义数组，存放表名
                string[] sheetNames = new string[dataTable.Rows.Count];

                // 循环
                int i = 0;
                foreach (System.Data.DataRow dataRow in dataTable.Rows)
                {
                    foreach (System.Data.DataColumn dataColumn in dataTable.Columns)
                    {
                        // 判断
                        if (dataRow[dataColumn].GetType().Name != "DBNull")
                            if (dataRow[dataColumn].ToString().ToUpper() != "TABLE")
                                if (dataRow[dataColumn].GetType().Name != "DateTime")
                                    // 将名称加入到数组
                                    sheetNames.SetValue(dataRow[dataColumn], i);
                    }
                    // 下一条记录
                    i++;
                }

                // 返回结果
                return sheetNames;
            }
            finally
            {
                if (xlsConn != null) if (xlsConn.State != System.Data.ConnectionState.Closed) xlsConn.Close();
            }
        }
        /// <summary>
        /// 读取指定表名的数据
        /// </summary>
        /// <param name="xlsFile">Excel文件</param>
        /// <param name="sheetName">工作表名</param>
        /// <returns>返回一个Dataset对象</returns>
        public System.Data.DataSet GetExcelDataSet(string sheetName)
        {
            return GetExcelDataSet(sheetName, "");
        }
        /// <summary>
        /// 读取指定表名的数据
        /// </summary>
        /// <param name="xlsFile">Excel文件</param>
        /// <param name="sheetName">工作表名</param>
        /// <returns>返回一个Dataset对象</returns>
        public System.Data.DataSet GetExcelDataSet(string sheetName, string filter)
        {
            if (this._dbConnection == null) return null;
            OleDbConnection xlsConn = (OleDbConnection)this._dbConnection;
            try
            {
                // 打开连接
                if (xlsConn.State == ConnectionState.Closed) xlsConn.Open();

                sheetName = sheetName.Trim('$');

                // 查询excel文件
                string sql = "Select * from [" + sheetName + "$]";
                System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter();
                dataAdapter.SelectCommand = new System.Data.OleDb.OleDbCommand(sql, xlsConn);

                //  取出表
                System.Data.DataSet xlsDataSet = new System.Data.DataSet();
                dataAdapter.Fill(xlsDataSet);

                // 返回结果
                return xlsDataSet;
            }
            finally
            {
                // 关闭数据连接 
                if (xlsConn != null) xlsConn.Close();
            }
        }
        /// <summary>
        /// 读取指定表名的数据
        /// </summary>
        /// <param name="xlsFile">Excel文件</param>
        /// <param name="sheetName">工作表名</param>
        /// <returns>返回一个DataTable对象</returns>
        public System.Data.DataTable GetExcelDataTable(string sheetName)
        {
            //  取出表
            System.Data.DataSet xlsDataSet = GetExcelDataSet(sheetName);
            // 判断
            if (xlsDataSet == null || xlsDataSet.Tables.Count <= 0) return null;

            // 返回结果
            return xlsDataSet.Tables[0];
        }
        /// <summary>
        /// 读取指定表名的数据
        /// </summary>
        /// <param name="xlsFile">Excel文件</param>
        /// <param name="sheetName">工作表名</param>
        /// <returns>返回一个DataTable对象</returns>
        public System.Data.DataTable GetExcelDataTable(string sheetName, string filter)
        {
            // 返回结果
            System.Data.DataSet ds = GetExcelDataSet(sheetName, filter);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            else return null;
        }

        #endregion

        #region 类内部方法

        /// <summary>
        /// 获取Excel文件的连接对象
        /// </summary>
        /// <param name="sourceFile">Excel文件路径</param>
        /// <param name="password">密码</param>
        /// <returns></returns>
        OleDbConnection GetExcelConnection(string sourceFile,  qiaoExcelVersion ver)
        {
            if (!System.IO.File.Exists(sourceFile)) return null;

            string connString = string.Empty;
            // 获取不同版本Excel文件的连接字符串
            switch (ver)
            {
                case  qiaoExcelVersion.Excel2003:
                case  qiaoExcelVersion.Unknow:
                    connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=excel 8.0;Persist Security Info=True", sourceFile);
                    break;
                case  qiaoExcelVersion.Excel2007:
                case  qiaoExcelVersion.Excel2010:
                    connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\"", sourceFile);
                    break;
            }
            // 返回连接对象
            return new OleDbConnection(connString);
        }

        /// <summary>
        /// 创建数据库操作对象
        /// </summary>
        /// <returns></returns>
        IDbCommand CreateExcelCommand()
        {
            IDbCommand dbCMD = _dbCommand;
            if (dbCMD == null)
            {
                if (this._dbConnection == null) return null;

                dbCMD = this._dbConnection.CreateCommand();
                // 打开数据库连接
                if (this._dbConnection.State == ConnectionState.Closed) this._dbConnection.Open();
            }

            // 返回结果
            return dbCMD;
        }

        #endregion
    }
}
